<?php
class PersistenceException extends Exception {

}


class SQLitePersistence {

	private $dataPath = 'data/winningDB.dat';

	public function SQLitePersistence() {
	}

	public function authenticate($username, $password) {
		$result = '';
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT count(*) FROM users WHERE username = '" . $username . "'" .
					 " AND password = '" . $password . "'";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			if ($row[0] == '1') {
				$result = true;
			} else {
				$result = false;
			}

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $result;
	}

	public function getAboutUsInfo() {
		$result = '';
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT DESC FROM ABOUTUS ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$result = $row[0];
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $result;
	}

	public function saveAboutUsInfo($aboutUsInfo) {
		$dbhandle = '';
		try {
			$aboutUsInfo = $this->trimString($aboutUsInfo);
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE ABOUTUS set DESC = '" . $aboutUsInfo . "'";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}


	public function getHeadOffice() {
		$result = '';
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT DESC FROM HEADOFFICE ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$result = $row[0];
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $result;
	}

	public function saveHeadOffice($headOfficeInfo) {
		$dbhandle = '';
		try {
			$headOfficeInfo = $this->trimString($headOfficeInfo);
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE HEADOFFICE set DESC = '" . $headOfficeInfo . "'";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function getShowroom() {
		$result = '';
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT DESC FROM SHOWROOM ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$result = $row[0];
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $result;
	}

	public function saveShowroom($showroomInfo) {
		$dbhandle = '';
		try {
			$showroomInfo = $this->trimString($showroomInfo);
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE SHOWROOM set DESC = '" . $showroomInfo . "'";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function deleteTable($tableName) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " DELETE FROM " . $tableName;
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function getAllAnnouncement() {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM ANNOUNCEMENT ORDER BY ANNOUNCEMENT_SEQ ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$anAnnouncement = new Announcement();
				$anAnnouncement->setAnnouncementId($row['announcement_id']);
				$anAnnouncement->setAnnouncementDesc($row['announcement_desc']);
				$anAnnouncement->setAnnouncementSeq($row['announcement_seq']);
				array_push($resultArr, $anAnnouncement);
				unset($anAnnouncement);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $resultArr;
	}

	public function deleteAllAnnouncement() {
		try {
			$this->deleteTable('ANNOUNCEMENT');
		} catch (PersistenceException $e) {
			throw $e;
		}
	}

	public function saveNewAnnouncement($announcementInfo) {
		$dbhandle = '';
		try {
			$announcementInfo = $this->trimString($announcementInfo);
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT MAX(announcement_seq) FROM ANNOUNCEMENT ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$maxSequenceNumber = $row[0];
			$maxSequenceNumber = $maxSequenceNumber + 1;

			$query = " INSERT INTO ANNOUNCEMENT (announcement_desc, announcement_seq) VALUES (" .
					 " '" . $announcementInfo . "','" . $maxSequenceNumber . "')";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function deleteAnnouncement($announcementID) {
		$this->deleteSequential($announcementID, 'ANNOUNCEMENT');
	}

	public function updateAnnouncementObj($announcementObj) {
		$dbhandle = '';
		try {
			$announcementObj->setAnnouncementDesc($this->trimString($announcementObj->getAnnouncementDesc()));
			
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE ANNOUNCEMENT SET " .
					 " announcement_seq = '" . $announcementObj->getAnnouncementSeq() . "', " .
					 " announcement_desc = '" . $announcementObj->getAnnouncementDesc() . "' " .
					 " WHERE " .
					 " announcement_id = '" . $announcementObj->getAnnouncementId() . "';";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function getAllFactory() {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM FACTORY ORDER BY FACTORY_SEQ ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$aFactory = new Factory();
				$aFactory->setFactoryId($row['factory_id']);
				$aFactory->setFactoryCountry($row['country']);
				$aFactory->setFactoryAddress($row['address']);
				$aFactory->setFactorySeq($row['factory_seq']);
				array_push($resultArr, $aFactory);
				unset($aFactory);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $resultArr;
	}

	public function saveNewFactory($factoryObj) {
		$dbhandle = '';
		try {
			$factoryObj->setFactoryCountry($this->trimString($factoryObj->getFactoryCountry()));
			$factoryObj->setFactoryAddress($this->trimString($factoryObj->getFactoryAddress()));
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT MAX(factory_seq) FROM FACTORY ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$maxSequenceNumber = $row[0];
			$maxSequenceNumber = $maxSequenceNumber + 1;

			$query = " INSERT INTO FACTORY (country, address, factory_seq) VALUES (" .
					 " '" . $factoryObj->getFactoryCountry() . "','" . $factoryObj->getFactoryAddress() . "','" . $maxSequenceNumber . "')";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function deleteFactory($factoryID) {
		$this->deleteSequential($factoryID, 'FACTORY');
	}

	public function updateFactoryObj($factoryObj) {
		$dbhandle = '';
		try {
			$factoryObj->setFactoryCountry($this->trimString($factoryObj->getFactoryCountry()));
			$factoryObj->setFactoryAddress($this->trimString($factoryObj->getFactoryAddress()));
			
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE FACTORY SET " .
					 " factory_seq = '" . $factoryObj->getFactorySeq() . "', " .
					 " country = '" . $factoryObj->getFactoryCountry() . "', " .
					 " address = '" . $factoryObj->getFactoryAddress() . "' " .
					 " WHERE " .
					 " factory_id = '" . $factoryObj->getFactoryId() . "';";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function getAllOutlet() {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM OUTLET ORDER BY OUTLET_SEQ ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$aOutlet = new Outlet();
				$aOutlet->setOutletId($row['outlet_id']);
				$aOutlet->setOutletLocation($row['location']);
				$aOutlet->setOutletAddress($row['address']);
				$aOutlet->setOutletTelephone($row['telephone']);
				$aOutlet->setOutletSeq($row['outlet_seq']);
				array_push($resultArr, $aOutlet);
				unset($aOutlet);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $resultArr;
	}

	public function saveNewOutlet($outletObj) {
		$dbhandle = '';
		try {
			$outletObj->setOutletLocation($this->trimString($outletObj->getOutletLocation()));
			$outletObj->setOutletAddress($this->trimString($outletObj->getOutletAddress()));
			$outletObj->setOutletTelephone($this->trimString($outletObj->getOutletTelephone()));
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT MAX(outlet_seq) FROM OUTLET ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_NUM);
			$maxSequenceNumber = $row[0];
			$maxSequenceNumber = $maxSequenceNumber + 1;

			$query = " INSERT INTO OUTLET (location, address, telephone, outlet_seq) VALUES (" .
					 " '" . $outletObj->getOutletLocation() . "','" . $outletObj->getOutletAddress() . "','" .
					 $outletObj->getOutletTelephone() . "','" .
					 $maxSequenceNumber . "')";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	private function deleteSequential($objectID, $type) {
		$tableName = '';
		$primaryKeyColumnName = '';
		$sequentialColumnName = '';
		if ($type == 'OUTLET') {
			$tableName = 'OUTLET';
			$primaryKeyColumnName = 'outlet_id';
			$sequentialColumnName = 'outlet_seq';
		} else if ($type == 'FACTORY') {
			$tableName = 'FACTORY';
			$primaryKeyColumnName = 'factory_id';
			$sequentialColumnName = 'factory_seq';
		} else if ($type == 'ANNOUNCEMENT') {
			$tableName = 'ANNOUNCEMENT';
			$primaryKeyColumnName = 'announcement_id';
			$sequentialColumnName = 'announcement_seq';
		}
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM " . $tableName . " ORDER BY " . $sequentialColumnName;
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$deleted = false;
			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				if ($row[$primaryKeyColumnName] == $objectID) {
					$query = " DELETE FROM " . $tableName . "  WHERE " . $primaryKeyColumnName . " = " . "'" . $objectID . "';";
					$status = sqlite_exec($dbhandle, $query);
					$deleted = true;
					if (!$status)
						throw new PersistenceException("Cannot execute statement.");
					continue;
				}
				if ($deleted) {
					$seqNo = $row[$sequentialColumnName] - 1;
					$query = " UPDATE " . $tableName . " SET " . $sequentialColumnName . " = '" . $seqNo . "' WHERE " .
							 $primaryKeyColumnName . " = '" . $row[$primaryKeyColumnName] . "';";
					$status = sqlite_exec($dbhandle, $query);
					if (!$status)
						throw new PersistenceException("Cannot execute statement.");
				}
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function deleteOutlet($outletID) {
		$this->deleteSequential($outletID, 'OUTLET');
	}

	public function updateOutletObj($outletObj) {
		$dbhandle = '';
		try {
			$outletObj->setOutletLocation($this->trimString($outletObj->getOutletLocation()));
			$outletObj->setOutletAddress($this->trimString($outletObj->getOutletAddress()));
			$outletObj->setOutletTelephone($this->trimString($outletObj->getOutletTelephone()));
			
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE OUTLET SET " .
					 " outlet_seq = '" . $outletObj->getOutletSeq() . "', " .
					 " location = '" . str_replace("'", "''", $outletObj->getOutletLocation()) . "', " .
					 " address = '" . str_replace("'", "''", $outletObj->getOutletAddress()) . "', " .
					 " telephone = '" . $outletObj->getOutletTelephone() . "' " .
					 " WHERE " .
					 " outlet_id = '" . $outletObj->getOutletId() . "';";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function trimString($str) {
		$str = trim($str);
		//remove the newline and carriage
		$str = preg_replace('/\r\n/','',$str);
		//single quote handling
		$str = preg_replace('/\'/','&#39;',$str);
		//double quote handling
		$str = preg_replace('/\"/','&#34;',$str);
		return $str;
	}

	public function saveNewProduct($productObj) {
		$dbhandle = '';
		try {
			$productObj->setProductCode($this->trimString($productObj->getProductCode()));
			$productObj->setSubcategory($this->trimString($productObj->getSubcategory()));
			$productObj->setProductName($this->trimString($productObj->getProductName()));
			$productObj->setDescription($this->trimString($productObj->getDescription()));
			$productObj->setUnitPrice($this->trimString($productObj->getUnitPrice()));
			$productObj->setPostage($this->trimString($productObj->getPostage()));
			$productObj->setColorOpt($this->trimString($productObj->getColorOpt()));

			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT PRODUCT_ID, PRODUCT_CODE FROM PRODUCT WHERE PRODUCT_CODE = '" . $productObj->getProductCode() . "' AND DEFUNCT_IND = 'N' ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_ASSOC);
			$prodId = $row['PRODUCT_ID'];
			if ($prodId != "") {
				return 1001; // return code 1001 if product code already exists
			}
			//if ($prodCode != "") {
			//	throw new PersistenceException('Insert failed! Product Code ' . $prodCode . ' already exists!');
			//}

			$query = " INSERT INTO PRODUCT (PRODUCT_CODE, CATEGORY, SUBCATEGORY, PRODUCT_NAME, DESCRIPTION, " .
					 " UNIT_PRICE, POST_ALLOW, POSTAGE, SIZE_OPT, COLOR_OPT, THUMBNAIL_PATH, " .
					 " IMG_PATH_1, DEFUNCT_IND) VALUES (" .
					 " '" . $productObj->getProductCode() . "','" . $productObj->getCategory() . "','" .
					 $productObj->getSubcategory() . "','" .
					 str_replace("'", "''", $productObj->getProductName()) . "','" .
					 str_replace("'", "''", $productObj->getDescription()) . "','" .
					 $productObj->getUnitPrice() . "','" .
					 $productObj->getPostAllow() . "','" .
					 $productObj->getPostage() . "','" .
					 $productObj->getSizeOpt() . "','" .
					 $productObj->getColorOpt() . "','" .
					 $productObj->getThumbnailPath() . "','" .
					 $productObj->getImgPath1() . "','" .
					 $productObj->getDefunctInd() . "')";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return 0;
	}

	public function isProdCodeExist($prodCode) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT PRODUCT_ID, PRODUCT_CODE FROM PRODUCT WHERE PRODUCT_CODE = '" . $prodCode . "' AND DEFUNCT_IND = 'N' ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			$row = sqlite_fetch_array($dbResult, SQLITE_ASSOC);
			$prodId = $row['PRODUCT_ID'];
			if ($prodId != "") {
				return 1;
			}

		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return 0;
	}

	public function getProductsByProdCode($prodCode) {
		$resultArr = array();
		$dbhandle = '';
		$aProduct = new Product();

		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM PRODUCT WHERE PRODUCT_CODE = '" . $prodCode . "' AND DEFUNCT_IND = 'N' ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			if ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {

				$aProduct->setProductId($row['PRODUCT_ID']);
				$aProduct->setProductCode($row['PRODUCT_CODE']);
				$aProduct->setCategory($row['CATEGORY']);
				$aProduct->setSubcategory($row['SUBCATEGORY']);
				$aProduct->setProductName($row['PRODUCT_NAME']);
				$aProduct->setDescription($row['DESCRIPTION']);
				$aProduct->setUnitPrice($row['UNIT_PRICE']);
				$aProduct->setPostAllow($row['POST_ALLOW']);
				$aProduct->setPostage($row['POSTAGE']);
				$aProduct->setSizeOpt($row['SIZE_OPT']);
				$aProduct->setColorOpt($row['COLOR_OPT']);
				$aProduct->setThumbnailPath($row['THUMBNAIL_PATH']);
				$aProduct->setImgPath1($row['IMG_PATH_1']);
				$aProduct->setDefunctInd($row['DEFUNCT_IND']);

			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $aProduct;
	}

	public function getProductsByProdId($prodId) {
		$resultArr = array();
		$dbhandle = '';
		$aProduct = new Product();

		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM PRODUCT WHERE PRODUCT_ID = '" . $prodId . "' ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			if ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {

				$aProduct->setProductId($row['PRODUCT_ID']);
				$aProduct->setProductCode($row['PRODUCT_CODE']);
				$aProduct->setCategory($row['CATEGORY']);
				$aProduct->setSubcategory($row['SUBCATEGORY']);
				$aProduct->setProductName($row['PRODUCT_NAME']);
				$aProduct->setDescription($row['DESCRIPTION']);
				$aProduct->setUnitPrice($row['UNIT_PRICE']);
				$aProduct->setPostAllow($row['POST_ALLOW']);
				$aProduct->setPostage($row['POSTAGE']);
				$aProduct->setSizeOpt($row['SIZE_OPT']);
				$aProduct->setColorOpt($row['COLOR_OPT']);
				$aProduct->setThumbnailPath($row['THUMBNAIL_PATH']);
				$aProduct->setImgPath1($row['IMG_PATH_1']);
				$aProduct->setDefunctInd($row['DEFUNCT_IND']);

			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $aProduct;
	}

	public function getProductsByCategory($category, $subcategory, $prodName) {

		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM PRODUCT WHERE DEFUNCT_IND = 'N' ";
			if ($category != "") {
				$query = $query . " AND CATEGORY = '" . $category . "' ";
			}
			if ($subcategory != "") {
				if (!$isFirstCriteria) {
					$query = $query . " AND ";
				}
				$query = $query . " AND SUBCATEGORY = '" . $subcategory . "' ";
			}
			if ($prodName != "") {
				if (!$isFirstCriteria) {
					$query = $query . " AND ";
				}
				$query = $query . " AND PRODUCT_NAME LIKE '%" . $prodName . "%' ";
			}
			$query = $query . " ORDER BY SUBCATEGORY, PRODUCT_CODE ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {

				$aProduct = new Product();
				$aProduct->setProductId($row['PRODUCT_ID']);
				$aProduct->setProductCode($row['PRODUCT_CODE']);
				$aProduct->setCategory($row['CATEGORY']);
				$aProduct->setSubcategory($row['SUBCATEGORY']);
				$aProduct->setProductName($row['PRODUCT_NAME']);
				$aProduct->setDescription($row['DESCRIPTION']);
				$aProduct->setUnitPrice($row['UNIT_PRICE']);
				$aProduct->setPostAllow($row['POST_ALLOW']);
				$aProduct->setPostage($row['POSTAGE']);
				$aProduct->setSizeOpt($row['SIZE_OPT']);
				$aProduct->setColorOpt($row['COLOR_OPT']);
				$aProduct->setThumbnailPath($row['THUMBNAIL_PATH']);
				$aProduct->setImgPath1($row['IMG_PATH_1']);
				$aProduct->setDefunctInd($row['DEFUNCT_IND']);

				array_push($resultArr, $aProduct);
				unset($aProduct);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $resultArr;
	}

	public function defunctProductsById($prodId) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE PRODUCT SET DEFUNCT_IND = 'Y' WHERE PRODUCT_ID = '" . $prodId . "'";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function updateProductImgPathById($aProduct) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE PRODUCT SET THUMBNAIL_PATH = '" . $aProduct->getThumbnailPath() . "', " .
			         " IMG_PATH_1 = '" . $aProduct->getImgPath1() . "' " .
			         " WHERE PRODUCT_ID = '" . $aProduct->getProductId() . "' ";

			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function updateProductById($aProduct) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE PRODUCT SET CATEGORY = '" . $aProduct->getCategory() . "', " .
			         " SUBCATEGORY = '" . $aProduct->getSubcategory() . "', " .
			         " PRODUCT_NAME = '" . str_replace("'", "''", $aProduct->getProductName()) . "', " .
			         " DESCRIPTION = '" . str_replace("'", "''", $aProduct->getDescription()) . "', " .
			         " UNIT_PRICE = '" . $aProduct->getUnitPrice() . "', " .
			         " POST_ALLOW = '" . $aProduct->getPostAllow() . "', " .
			         " POSTAGE = '" . $aProduct->getPostage() . "', " .
			         " SIZE_OPT = '" . $aProduct->getSizeOpt() . "', " .
			         " COLOR_OPT = '" . $aProduct->getColorOpt() . "' " .
			         " WHERE PRODUCT_ID = '" . $aProduct->getProductId() . "' ";
			$status = sqlite_exec($dbhandle, $query);

			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}
	
	public function getOrdersHeaderByStatus($status) {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM order_header WHERE status = '" . $status .  "' ORDER BY updated_dt, created_dt DESC ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$orderHeader = new OrderHeader();
				$orderHeader->setOrderId($row['ORDER_ID']);
				$orderHeader->setCustName($row['CUST_NAME']);
				$orderHeader->setCustContact($row['CUST_CONTACT']);
				$orderHeader->setCustAddress($row['CUST_ADDRESS']);
				$orderHeader->setCustEmail($row['CUST_EMAIL']);
				$orderHeader->setTotalAmt($row['TOTAL_AMT']);
				$orderHeader->setShipmentFee($row['SHIPMENT_FEE']);
				$orderHeader->setDeliveryMode($row['DELIVERY_MODE']);
				$orderHeader->setPaymentMode($row['PAYMENT_MODE']);
				$orderHeader->setStatus($row['STATUS']);
				$orderHeader->setRemark($row['REMARK']);
				$orderHeader->setOrderDt($row['ORDER_DT']);
				$orderHeader->setCreatedDt($row['CUST_NAME']);
				$orderHeader->setUpdatedDt($row['UPDATED_DT']);				
				$orderHeader->setCreatedDt($row['CREATED_DT']);
				array_push($resultArr, $orderHeader);
				unset($orderHeader);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return $resultArr;
	}
	
	public function getAllOrdersHeader() {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM order_header WHERE status = 'NEW' ORDER BY updated_dt, created_dt DESC ";
			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$orderHeader = new OrderHeader();
				$orderHeader->setOrderId($row['ORDER_ID']);
				$orderHeader->setCustName($row['CUST_NAME']);
				$orderHeader->setCustContact($row['CUST_CONTACT']);
				$orderHeader->setCustAddress($row['CUST_ADDRESS']);
				$orderHeader->setCustEmail($row['CUST_EMAIL']);
				$orderHeader->setTotalAmt($row['TOTAL_AMT']);
				$orderHeader->setShipmentFee($row['SHIPMENT_FEE']);
				$orderHeader->setDeliveryMode($row['DELIVERY_MODE']);
				$orderHeader->setPaymentMode($row['PAYMENT_MODE']);
				$orderHeader->setStatus($row['STATUS']);
				$orderHeader->setRemark($row['REMARK']);
				$orderHeader->setOrderDt($row['ORDER_DT']);
				$orderHeader->setCreatedDt($row['CUST_NAME']);
				$orderHeader->setUpdatedDt($row['UPDATED_DT']);				
				$orderHeader->setCreatedDt($row['CREATED_DT']);
				array_push($resultArr, $orderHeader);
				unset($orderHeader);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return $resultArr;
	}
	
	public function getOrderHeaderByID($orderID) {
		$result = new OrderHeader();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM ORDER_HEADER WHERE ORDER_ID = '" . $orderID . "' ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			if ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$result->setOrderId($row['ORDER_ID']);
				$result->setCustName($row['CUST_NAME']);
				$result->setCustContact($row['CUST_CONTACT']);
				$result->setCustAddress($row['CUST_ADDRESS']);
				$result->setCustEmail($row['CUST_EMAIL']);
				$result->setTotalAmt($row['TOTAL_AMT']);
				$result->setShipmentFee($row['SHIPMENT_FEE']);
				$result->setDeliveryMode($row['DELIVERY_MODE']);
				$result->setPaymentMode($row['PAYMENT_MODE']);
				$result->setStatus($row['STATUS']);
				$result->setRemark($row['REMARK']);
				$result->setOrderDt($row['ORDER_DT']);
				$result->setCreatedDt($row['CREATED_DT']);
				$result->setUpdatedDt($row['UPDATED_DT']);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return $result;
	}
	
	public function getLatestOrderID() {
		$result = '';
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT MAX(ORDER_ID) as LATEST_ID FROM ORDER_HEADER ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			if ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$result = $row['LATEST_ID'];
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return $result;
	}
	
	public function getOrderDetailsByID($orderID) {
		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM ORDER_DETAIL WHERE ORDER_ID = '" . $orderID . "' ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$aOrderDetail = new OrderDetail();
				$aOrderDetail->setOrderId($row['ORDER_ID']);
				$aOrderDetail->setProductId($row['PRODUCT_ID']);
				$aProduct = $this->getProductsByProdId($aOrderDetail->getProductId());	
				$aOrderDetail->setProductCode($aProduct->getProductCode());
				$aOrderDetail->setUnitPrice($row['UNIT_PRICE']);
				$aOrderDetail->setQuantity($row['QUANTITY']);
				$aOrderDetail->setSizeSelected($row['SIZE_SELECTED']);
				$aOrderDetail->setColorSelected($row['COLOR_SELECTED']);
				array_push($resultArr, $aOrderDetail);
				unset($aOrderDetail);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return $resultArr;
	}
	
	public function updateOrderHeader($orderHeader) {
		$orderHeader->setUpdatedDt(date("Y-m-d H:i:s"));

		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " UPDATE order_header SET " . 
					 " UPDATED_DT='" . $orderHeader->getUpdatedDt() . "'," .
					 " STATUS='" . $orderHeader->getStatus() . "' " .
					 " WHERE " .
					 " ORDER_ID='" . $orderHeader->getOrderId() . "'";
			$status = sqlite_exec($dbhandle, $query);
			if (!$status)
				throw new PersistenceException("Cannot execute statement.");
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
	}

	public function saveOrderHeader($orderHeader) {
		$dbhandle = '';
		try {
			$orderHeader->setCustName($this->trimString($orderHeader->getCustName()));
			$orderHeader->setCustContact($this->trimString($orderHeader->getCustContact()));
			$orderHeader->setCustEmail($this->trimString($orderHeader->getCustEmail()));

			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = "INSERT INTO order_header (cust_name, cust_contact, cust_address, cust_email, total_amt, shipment_fee, " .
					 "delivery_mode, payment_mode, status, remark, order_dt, created_dt, updated_dt) VALUES ( " .
					 "'" . str_replace("'", "''", $orderHeader->getCustName()) . "', " .
					 "'" . $orderHeader->getCustContact() . "', " .
					 "'" . str_replace("'", "''", $orderHeader->getCustAddress()) . "', " .
					 "'" . $orderHeader->getCustEmail() . "', " .
					 "'" . $orderHeader->getTotalAmt() . "', " .
					 "'" . $orderHeader->getShipmentFee() . "', " .
					 "'" . $orderHeader->getDeliveryMode() . "', " .
					 "'" . $orderHeader->getPaymentMode() . "', " .
					 "'" . $orderHeader->getStatus() . "', " .
					 "'" . str_replace("'", "''", $orderHeader->getRemark()) . "', " .
					 "'" . $orderHeader->getOrderDt() . "', " .
					 "'" . $orderHeader->getCreatedDt() . "', " .
					 "'" . $orderHeader->setUpdatedDt() . "') ";

			$status = sqlite_exec($dbhandle, $query);

			if (!$status)
				throw new PersistenceException("Cannot execute statement.");

			$query = "SELECT order_id FROM order_header WHERE cust_email = '" .
					 $orderHeader->getCustEmail() . "' AND order_dt = '" .
					 $orderHeader->getOrderDt() . "'";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');


			if ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {
				$orderHeader->setOrderId($row['order_id']);
			}

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return 0;
	}

	public function saveOrderDetail($orderHeader, $arrOrderDetail) {
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			foreach ($arrOrderDetail as $orderDetail)
			{
				$query = "INSERT INTO order_detail (order_id, product_id, unit_price, quantity, color_selected, size_selected) " .
						 "VALUES ( " .
						 "'" . $orderHeader->getOrderId() . "', " .
						 "'" . $orderDetail->getProductId() . "', " .
						 "'" . $orderDetail->getUnitPrice() . "', " .
						 "'" . $orderDetail->getQuantity() . "', " .
						 "'" . $orderDetail->getColorSelected() . "', " .
						 "'" . $orderDetail->getSizeSelected() . "') ";

				$orderDetail->setOrderId($orderHeader->getOrderId());
				$status = sqlite_exec($dbhandle, $query);

				if (!$status)
					throw new PersistenceException("Cannot execute statement.");
			}

			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}
		return 0;
	}

	public function saveOrder($orderHeader, $arrOrderDetail) {
		$dbhandle = '';
		try {
			$this->saveOrderHeader($orderHeader);
			$this->saveOrderDetail($orderHeader, $arrOrderDetail);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			return 1;
		}
		return 0;
	}
	
	public function getOrderHeader($email, $custContact) {
		$dbhandle = '';
		$orderHeader = new OrderHeader();
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT * FROM ORDER_HEADER WHERE CUST_EMAIL = '" . $email . "' AND CUST_CONTACT = '" . $custContact . "' ORDER BY ORDER_ID DESC LIMIT 1 ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');
			
			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {

				$orderHeader = new OrderHeader();
				$orderHeader->setOrderId($row['ORDER_ID']);
				$orderHeader->setCustName($row['CUST_NAME']);
				$orderHeader->setCustContact($row['CUST_CONTACT']);
				$orderHeader->setCustAddress($row['CUST_ADDRESS']);
				$orderHeader->setCustEmail($row['CUST_EMAIL']);
				$orderHeader->setTotalAmt($row['TOTAL_AMT']);
				$orderHeader->setShipmentFee($row['SHIPMENT_FEE']);
				$orderHeader->setDeliveryMode($row['DELIVERY_MODE']);
				$orderHeader->setPaymentMode($row['PAYMENT_MODE']);
				$orderHeader->setStatus($row['STATUS']);
				$orderHeader->setRemark($row['REMARK']);
				$orderHeader->setOrderDt($row['ORDER_DT']);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $orderHeader;
	}
	
	public function getOrderDetail($orderId) {

		$resultArr = array();
		$dbhandle = '';
		try {
			$dbhandle = sqlite_open($this->dataPath, 0666, $error);
			if (!$dbhandle)
				throw new PersistenceException($error);

			$query = " SELECT OD.ORDER_ID, PRD.PRODUCT_CODE, PRD.PRODUCT_NAME, PRD.POSTAGE, " .
				" PRD.POST_ALLOW, OD.SIZE_SELECTED, OD.COLOR_SELECTED, OD.QUANTITY, OD.UNIT_PRICE " .
				" FROM ORDER_DETAIL OD, PRODUCT PRD " .
				" WHERE ORDER_ID = '" . $orderId . "' AND PRD.PRODUCT_ID = OD.PRODUCT_ID " .
				" ORDER BY PRODUCT_CODE ";

			$dbResult = sqlite_query($dbhandle, $query);
			if (!$dbResult)
				throw new PersistenceException('Cannot execute query');

			while ($row = sqlite_fetch_array($dbResult, SQLITE_ASSOC)) {				

				$orderDetail = new OrderDetail();
				$orderDetail->setOrderId($row['OD.ORDER_ID']);
				$orderDetail->setProductCode($row['PRD.PRODUCT_CODE']);
				$orderDetail->setProductName($row['PRD.PRODUCT_NAME']);
				$orderDetail->setPostage($row['PRD.POSTAGE']);
				$orderDetail->setPostAllow($row['PRD.POST_ALLOW']);
				$orderDetail->setSizeSelected($row['OD.SIZE_SELECTED']);
				$orderDetail->setColorSelected($row['OD.COLOR_SELECTED']);
				$orderDetail->setQuantity($row['OD.QUANTITY']);
				$orderDetail->setUnitPrice($row['OD.UNIT_PRICE']);

				array_push($resultArr, $orderDetail);
				unset($orderDetail);
			}
			sqlite_close($dbhandle);
		} catch (Exception $e) {
			sqlite_close($dbhandle);
			throw new PersistenceException($e);
		}

		return $resultArr;
	}

}

?>